import numpy as np
import pandas as pd
import requests
import json
import plotly.express as px
import dash
from datetime import timedelta
endpoint = 'https://data.virginia.gov/resource/bre9-aqqr.json'
mypars = {'$limit': 150000}
headers = {'User-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:99.0) Gecko/20100101 Firefox/99.0'}
r = requests.get(endpoint, params=mypars, headers=headers)
r
<Response [200]>
cases = pd.json_normalize(json.loads(r.text))
cases
| report_date | fips | locality | vdh_health_district | total_cases | hospitalizations | deaths | |
|---|---|---|---|---|---|---|---|
| 0 | 2022-07-18T00:00:00.000 | 51001 | Accomack | Eastern Shore | 7628 | 417 | 108 |
| 1 | 2022-07-18T00:00:00.000 | 51003 | Albemarle | Blue Ridge | 21101 | 489 | 173 |
| 2 | 2022-07-18T00:00:00.000 | 51005 | Alleghany | Alleghany | 3667 | 97 | 100 |
| 3 | 2022-07-18T00:00:00.000 | 51007 | Amelia | Piedmont | 2900 | 169 | 59 |
| 4 | 2022-07-18T00:00:00.000 | 51009 | Amherst | Central Virginia | 7998 | 349 | 93 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 113577 | 2020-03-17T00:00:00.000 | 51800 | Suffolk | Western Tidewater | 0 | 0 | 0 |
| 113578 | 2020-03-17T00:00:00.000 | 51810 | Virginia Beach | Virginia Beach | 4 | 0 | 0 |
| 113579 | 2020-03-17T00:00:00.000 | 51820 | Waynesboro | Central Shenandoah | 0 | 0 | 0 |
| 113580 | 2020-03-17T00:00:00.000 | 51830 | Williamsburg | Peninsula | 1 | 0 | 0 |
| 113581 | 2020-03-17T00:00:00.000 | 51840 | Winchester | Lord Fairfax | 0 | 0 | 0 |
113582 rows × 7 columns
cases['report_date'] = pd.to_datetime(cases['report_date'])
cases['total_cases'] = cases['total_cases'].astype('int')
cases['hospitalizations'] = cases['hospitalizations'].astype('int')
cases['deaths'] = cases['deaths'].astype('int')
cases_today = cases.loc[cases['report_date'] == max(cases['report_date'])]
url = "https://demographics.coopercenter.org/sites/demographics/files/media/files/2020-07/Census_2019_RaceEstimates_forVA_0.xls"
pop = pd.read_excel(url, skiprows=4)
pop = pop.loc[~pop['FIPS'].isna()]
pop['FIPS'] = pop['FIPS'] + 51000
pop['FIPS'] = pop['FIPS'].astype('int').astype('str')
pop = pop[['FIPS', 'Total Population']]
pop
| FIPS | Total Population | |
|---|---|---|
| 3 | 51001 | 32316.0 |
| 4 | 51003 | 109330.0 |
| 5 | 51005 | 14860.0 |
| 6 | 51007 | 13145.0 |
| 7 | 51009 | 31605.0 |
| ... | ... | ... |
| 131 | 51800 | 92108.0 |
| 132 | 51810 | 449974.0 |
| 133 | 51820 | 22630.0 |
| 134 | 51830 | 14954.0 |
| 135 | 51840 | 28078.0 |
133 rows × 2 columns
cases_pop = pd.merge(cases_today, pop,
left_on = ['fips'],
right_on = ['FIPS'],
how = 'inner')
cases_pop['Cases per 1000 people'] = round(1000*cases_pop['total_cases']/cases_pop['Total Population'],1)
cases_pop['Hospitalizations per 1000 people'] = round(1000*cases_pop['hospitalizations']/cases_pop['Total Population'],1)
cases_pop['Deaths per 1000 people'] = round(1000*cases_pop['deaths']/cases_pop['Total Population'],1)
r = requests.get('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json')
counties = json.loads(r.text)
fig = px.choropleth(cases_pop, geojson=counties, locations='fips',
color='Cases per 1000 people',
color_continuous_scale="Viridis",
scope="usa",
labels={'total_cases':'Cases per 1000 people'},
hover_name = 'locality'
)
fig.update_geos(fitbounds="locations")
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show()
cases_table = cases_pop[['locality', 'Cases per 1000 people',
'Hospitalizations per 1000 people',
'Deaths per 1000 people']]
dash.dash_table.DataTable(
id='datatable-interactivity',
columns=[
{"name": i, "id": i, "deletable": True, "selectable": True} for i in cases_table.columns
],
data=cases_table,
editable=True,
filter_action="native",
sort_action="native",
sort_mode="multi",
column_selectable="single",
row_selectable="multi",
row_deletable=True,
selected_columns=[],
selected_rows=[],
page_action="native",
page_current= 0,
page_size= 10)
DataTable(data= locality Cases per 1000 people Hospitalizations per 1000 people \
0 Accomack 236.0 12.9
1 Albemarle 193.0 4.5
2 Alleghany 246.8 6.5
3 Amelia 220.6 12.9
4 Amherst 253.1 11.0
.. ... ... ...
128 Suffolk 227.4 9.0
129 Virginia Beach 223.0 7.6
130 Waynesboro 284.9 7.6
131 Williamsburg 131.7 4.1
132 Winchester 245.1 7.2
Deaths per 1000 people
0 3.3
1 1.6
2 6.7
3 4.5
4 2.9
.. ...
128 3.4
129 1.9
130 3.0
131 1.5
132 2.9
[133 rows x 4 columns], columns=[{'name': 'locality', 'id': 'locality', 'deletable': True, 'selectable': True}, {'name': 'Cases per 1000 people', 'id': 'Cases per 1000 people', 'deletable': True, 'selectable': True}, {'name': 'Hospitalizations per 1000 people', 'id': 'Hospitalizations per 1000 people', 'deletable': True, 'selectable': True}, {'name': 'Deaths per 1000 people', 'id': 'Deaths per 1000 people', 'deletable': True, 'selectable': True}], editable=True, column_selectable='single', row_selectable='multi', row_deletable=True, selected_rows=[], selected_columns=[], page_action='native', page_current=0, page_size=10, filter_action='native', sort_action='native', sort_mode='multi', id='datatable-interactivity')
cases_trend = cases[['report_date', 'fips', 'locality', 'total_cases', 'hospitalizations', 'deaths']]
cases_trend = pd.melt(cases_trend, id_vars = ['report_date', 'fips', 'locality'],
value_vars = ['total_cases', 'hospitalizations', 'deaths'])
cases_trend['date14'] = cases_trend['report_date'] + timedelta(14)
cases_trend['date28'] = cases_trend['report_date'] + timedelta(28)
cases_trend = pd.merge(cases_trend, cases_trend,
right_on = ['report_date', 'fips', 'locality', 'variable'],
left_on = ['date14', 'fips', 'locality', 'variable'])
cases_trend = cases_trend.drop(['report_date_x','date14_x','date28_x'], axis=1)
cases_trend = cases_trend.rename({'report_date_y':'report_date',
'date14_y':'date14',
'date28_y':'date28',
'value_y':'value',
'value_x':'value14'}, axis=1)
cases_trend = pd.merge(cases_trend, cases_trend,
right_on = ['report_date', 'fips', 'locality', 'variable'],
left_on = ['date28', 'fips', 'locality', 'variable'])
cases_trend = cases_trend.drop(['report_date_x','date14_y','date28_y', 'value14_x', 'date14_x', 'date28_x'], axis=1)
cases_trend = cases_trend.rename({'report_date_y':'report_date',
'value_y':'value',
'value14_y':'value14',
'value_x':'value28'}, axis=1)
#cases_trend = cases_trend.loc[cases_trend['report_date'] == max(cases_trend['report_date'])]
cases_trend['Most recent 14 days'] = cases_trend['value'] - cases_trend['value14']
cases_trend['Previous 14 days'] = cases_trend['value14'] - cases_trend['value28']
cases_trend['Trend'] = round(100*(cases_trend['Most recent 14 days'] - cases_trend['Previous 14 days']) / cases_trend['Previous 14 days'], 1)
cases_trend['Trend'] = cases_trend['Trend'].astype('str') + "%"
cases_trend
| fips | locality | variable | value28 | value14 | report_date | value | Most recent 14 days | Previous 14 days | Trend | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 51001 | Accomack | total_cases | 7325 | 7456 | 2022-07-18 | 7628 | 172 | 131 | 31.3% |
| 1 | 51003 | Albemarle | total_cases | 20284 | 20719 | 2022-07-18 | 21101 | 382 | 435 | -12.2% |
| 2 | 51005 | Alleghany | total_cases | 3480 | 3552 | 2022-07-18 | 3667 | 115 | 72 | 59.7% |
| 3 | 51007 | Amelia | total_cases | 2802 | 2850 | 2022-07-18 | 2900 | 50 | 48 | 4.2% |
| 4 | 51009 | Amherst | total_cases | 7675 | 7807 | 2022-07-18 | 7998 | 191 | 132 | 44.7% |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 323983 | 51800 | Suffolk | deaths | 0 | 1 | 2020-04-28 | 8 | 7 | 1 | 600.0% |
| 323984 | 51810 | Virginia Beach | deaths | 2 | 5 | 2020-04-28 | 12 | 7 | 3 | 133.3% |
| 323985 | 51820 | Waynesboro | deaths | 0 | 0 | 2020-04-28 | 0 | 0 | 0 | nan% |
| 323986 | 51830 | Williamsburg | deaths | 1 | 1 | 2020-04-28 | 1 | 0 | 0 | nan% |
| 323987 | 51840 | Winchester | deaths | 0 | 0 | 2020-04-28 | 0 | 0 | 0 | nan% |
323988 rows × 10 columns
loc = 'Alleghany'
today = max(cases_trend['report_date']).strftime('%B %d, %Y')
title = f"COVID Statistics for {loc} through {today}"
cases_loc = cases_trend.query(f"locality == '{loc}'")
fig = px.line(cases_loc, x='report_date', y='value', color='variable', facet_row='variable',
hover_data=['Most recent 14 days', 'Previous 14 days', 'Trend'],
labels={'report_date':'Date',
'value':'Total'},
title = title,
width=1000, height=800)
fig.update(layout=dict(title=dict(x=0.5)))
fig.update_layout(showlegend=False)
fig.for_each_annotation(lambda a: a.update(text=a.text.replace("variable=", "")))
fig.update_yaxes(matches=None)
fig.show()
tab = cases_loc.loc[cases_loc['report_date'] == max(cases_loc['report_date'])][['variable',
'Most recent 14 days',
'Previous 14 days',
'Trend']]
dash.dash_table.DataTable(
id='datatable-interactivity',
columns=[
{"name": i, "id": i, "deletable": True, "selectable": True} for i in tab.columns
],
data=tab,
editable=True,
filter_action="native",
sort_action="native",
sort_mode="multi",
column_selectable="single",
row_selectable="multi",
row_deletable=True,
selected_columns=[],
selected_rows=[],
page_action="native",
page_current= 0,
page_size= 10)
DataTable(data= variable Most recent 14 days Previous 14 days Trend
2 total_cases 115 72 59.7%
107998 hospitalizations 2 1 100.0%
215994 deaths 3 0 inf%, columns=[{'name': 'variable', 'id': 'variable', 'deletable': True, 'selectable': True}, {'name': 'Most recent 14 days', 'id': 'Most recent 14 days', 'deletable': True, 'selectable': True}, {'name': 'Previous 14 days', 'id': 'Previous 14 days', 'deletable': True, 'selectable': True}, {'name': 'Trend', 'id': 'Trend', 'deletable': True, 'selectable': True}], editable=True, column_selectable='single', row_selectable='multi', row_deletable=True, selected_rows=[], selected_columns=[], page_action='native', page_current=0, page_size=10, filter_action='native', sort_action='native', sort_mode='multi', id='datatable-interactivity')